﻿using MyCourseManageProject.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyCourseManageProject.Dal.Service
{
    /// <summary>
    /// 课程数据访问类
    /// </summary>
    public class CoursesDal
    {
        #region 添加
        ///// <summary>
        ///// 添加课程(SQL语句)
        ///// </summary>
        ///// <param name="cs">课程对象</param>
        ///// <returns>返回受影响的行数(正常情况1)</returns>
        //public int AddCourses(Courses cs)
        //{
        //    //1定义SQL语句(带参数);
        //    string sql = "insert into Courses(CourseName,CourseCountent,ClassHour,Credit,CategoryId,TeacherId)";
        //    sql += "values(@CourseName,@CourseCountent,@ClassHour,@Credit,@CategoryId,@TeacherId)";

        //    //2封装SQL语句  同时解析数组
        //    SqlParameter[] param = new SqlParameter[]{
        //        new SqlParameter("@CourseName",cs.CourseName),
        //        new SqlParameter("@CourseCountent",cs.CourseCountent),
        //        new SqlParameter("@ClassHour",cs.ClassHour),
        //        new SqlParameter("@Credit",cs.Credit),
        //        new SqlParameter("@CategoryId",cs.CategoryId),
        //        new SqlParameter("@TeacherId",cs.TeacherId)
        //    };
        //    //3执行SQL语句
        //    return SQLHelper.Update(sql, param);
        //}


        /// <summary>
        /// 添加课程(调用存储过程)
        /// </summary>
        /// <param name="cs">课程对象</param>
        /// <returns>返回受影响的行数(正常情况1)</returns>
        public int AddCourses(Courses cs)
        {

            //1封装SQL语句  同时解析数组
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@CourseName",cs.CourseName),
                new SqlParameter("@CourseCountent",cs.CourseCountent),
                new SqlParameter("@ClassHour",cs.ClassHour),
                new SqlParameter("@Credit",cs.Credit),
                new SqlParameter("@CategoryId",cs.CategoryId),
                new SqlParameter("@TeacherId",cs.TeacherId)
            };
            //2调用存储过程
            return SQLHelper.Update("ups_AddCourse", param, true);
        }
        #endregion


        /// <summary>
        /// 查询数据对象集合
        /// </summary>
        /// <param name="categroyId"></param>
        /// <param name="courseName"></param>
        /// <returns></returns>
        public List<Courses> GetAll(int categoryId, string courseName)
        {
            //1定义sql语句
            string sql = string.Format(@"select CourseId, CourseName,CourseCountent,ClassHour,Credit,CategoryId,Courses.TeacherId from Courses ");
            sql += " inner join Teachers on Teachers.TeacherId =Courses.TeacherId where 1=1";
            //where 1=1适合数据量不大的地方(几万条)  如果数据量大,建议字符串组合完成
            //2定义组合条件SQL语句
            if (categoryId != -1)
            {
                sql += string.Format(@" and CategoryId=" + categoryId);
            }
            if (!string.IsNullOrEmpty(courseName))
            {
                sql += string.Format(@" and CourseName like '%{0}%'", courseName);
            }
            //有条件继续拼接....
            //3执行SQL查询语句
            SqlDataReader reader = SQLHelper.GetReader(sql);
            //4将查询结果封装
            List<Courses> clist = new List<Courses>();
            while (reader.Read())
            {
                Courses cs = new Courses();
                cs.CourseId = (int)reader["CourseId"];
                cs.CourseName = reader["CourseName"].ToString();
                cs.CourseCountent = reader["CourseCountent"].ToString();
                cs.ClassHour = (int)reader["ClassHour"];
                cs.Credit = (int)reader["Credit"];
                cs.CategoryId = (int)reader["CategoryId"];
                cs.TeacherId = (int)reader["TeacherId"];
                //cs.TeacherName = reader["Teachers.TeacherName"].ToString();
                //cs.CourseId  = Convert.ToInt32(reader["CourseId"]);
                //cs.CourseName = reader["CourseName"].ToString();
                //cs.CourseCountent = reader["CourseCountent"].ToString();
                //cs.ClassHour = Convert.ToInt32(reader["ClassHour"]);
                //cs.Credit = Convert.ToInt32(reader["Credit"]);
                //cs.CategoryId = Convert.ToInt32(reader["CategoryId"]);
                //cs.TeacherId = Convert.ToInt32(reader["TeacherId"]);
                //TeacherName = reader["TeacherName"].ToString()

                clist.Add(cs);
            }
            reader.Close();
            return clist;
        }

        /// <summary>
        /// 修改课程数据
        /// </summary>
        /// <param name="cs"></param>
        /// <returns></returns>
        public int UpdateCourses(Courses cs)
        {
            //1定义SQL语句(带参数);
            string sql =string.Format(@"update Courses set CourseName=@CourseName,CourseCountent=@CourseCountent,ClassHour=@ClassHour,Credit=@Credit,CategoryId=@CategoryId,TeacherId=@TeacherId");
            sql += string.Format(" where CourseId=@CourseId");


            //2封装SQL语句  同时解析数组
            SqlParameter[] param = new SqlParameter[]{
                 new SqlParameter("@CourseId",cs.CourseId),
                new SqlParameter("@CourseName",cs.CourseName),
                new SqlParameter("@CourseCountent",cs.CourseCountent),
                new SqlParameter("@ClassHour",cs.ClassHour),
                new SqlParameter("@Credit",cs.Credit),
                new SqlParameter("@CategoryId",cs.CategoryId),
                new SqlParameter("@TeacherId",cs.TeacherId)
            };
            //3执行SQL语句
            return SQLHelper.Update(sql, param);
        }

        #region 删除
        ///// <summary>
        ///// 删除课程(SQL语句)
        ///// </summary>
        ///// <param name="cs"></param>
        ///// <returns></returns>
        //public int DelectCourses(Courses cs)
        //{
        //    string sql = string.Format(@"DELETE FROM [Courses]WHERE CourseId=" + cs.CourseId);


        //    return SQLHelper.Update(sql);
        //}
        /// <summary>
        /// 删除课程(存储过程)
        /// </summary>
        /// <param name="cs"></param>
        /// <returns></returns>
        public int DelectCourses(Courses cs)
        {

            //1封装SQL语句  同时解析数组
            SqlParameter[] param = new SqlParameter[]{
                     new SqlParameter("@CourseId",cs.CourseId)
                 };

            //2调用存储过程
            return SQLHelper.Update("ups_DelectCourse", param, true);
        }
        #endregion
    }
}
